﻿using System;
using System.Data;
using System.Collections;
using System.Collections.Specialized;
using WP.DB;
using WP.RM.DataEntity;
using WP.Commons;

namespace WP.RM.DataAccess
{
    public class RMProductBOMRevDAL : IDataAccess
    {
        public RMProductBOMRevDAL()
		{
		}
        #region Select List
        public string SQLList(int iParameter)
        {
            //string strSQL = @"select RecID,CategoryName,SetName ,ProductName,VersionNo,CreateDate,Creator,TotalCost,TotalCostRev,TotalCostDiff,StandardUnitCost,VerUnitCost,StandardUnitCost-VerUnitCost as [差异] from rmProductBOMRev order by ProductName, VersionNo";
            string strSQL = @"select RecID,CategoryName,SetName,ProductID ,ProductName,TechnicsName,VersionNo,CreateDate,Creator,StandardUnitCost,VerUnitCost,Quantity,VerUnitCost-StandardUnitCost as [差异] from rmProductBOMRev where VerClosed=0 order by ProductName, VersionNo";
            return strSQL;
        }
        #endregion

        #region Fields
        public string Fields
        {
            get
            {
                return @" CategoryID,SetID,ProductFItemID,CategoryName,SetName,ProductName, VersionNo,VersionRemark,CreateDate,Creator,
                          TotalLabourCost,TotalWastageCost,TotalDetailCost,TotalWorkCost,TotalCost,Remark, TechnicsName,
                          TotalLabourCostRev,TotalWastageCostRev,TotalDetailCostRev,TotalWorkCostRev,TotalCostRev,RemarkRev,
                          TotalLabourCostDiff,TotalWastageCostDiff,TotalDetailCostDiff,TotalWorkCostDiff,TotalCostDiff,RemarkDiff,
                          VerClosed,StartCost,VersionCost,OtherCost ,AvgUnitCost,ProductID  ";
            }
        }
        #endregion

        public string ParameterFields
        {
            get
            {
                return @"Quantity,
                            BaseSetSize,
                            BaseProductLength,
                            BaseProductWidth,
                            BaseMaterialLength,
                            BaseMaterialWidth,
                            BaseMaterialWeight,

                            InnerCirclePaperWidth,
                            InnerWeight,
                            InnerHeight,
                            InnerSetSize,
                            InnerPages,
                            InnerCutFactor,
                            InnerJoinWidth,
                            InnerProdLength,
                            InnerProdWidth,
                            InnerCutSize,
                            InnerJoinLength,

                            FrontCoverHeight,
                            BackCoverHeight,
                            FrontCoverSize,
                            BackCoverSize,
                            BackMateriallFactor,
                            BackPSFactor,
                            PagePrintLength,
                            PagePrintWidth,
                            CoverJoinLength,
                            CoverJoinWidth,
                            CoverMaterialLength,
                            CoverMaterialWidth,
                            BackPSQty1,
                            BackPSQty2,
                            BackPSQty3,
                            BackPSQty4,
                            BackPrintInnerQty,
                            BackPrintInnerQty2,
                            BackCutFactor1,
                            BackCutFactor2,
                            OthersBackQty,
                            OthersBackPaperQty,
                            LaminatingLength,
                            LaminatingWidth,

                            HCWeight,
                            HCSetSize,
                            HCHeight,
                            HCPages,

                            StEDPageWeigth,
                            StEDPageSetSize,
                            StEDPageHeight,
                            StEDPagePages,
                            StEDPageCirclePaperWidth,

                            PKGContainQty,
                            PKGPackageQty,
                            PKGInnerDiameterLength,
                            PKGInnerDiameterWidth,
                            PKGInnerDiameterHeight,
                            PKGRows,
                            PKGQty,

                            OthersLockingWireQty,
                            OthersLockingWireLength,
                            OthersBackWidth,
                            OthersUseIronNailQty,
                            OthersMiddleWidth,
                            OthersSheetSize,
                            OthersBurrWidth,
                            OthersWireLength,
                            OthersMiddleSize,
                            OthersSheetLength,
                            OthersSheetWidth,
                            OthersOneSheetLength,

                            OthersCopyPaperPS1,
                            OthersCopyPaperPS2,
                            PKGInQty,
                            PKGInLength,
                            PKGInWidth,
                            PKGInHeight,
                            HCJoinLength,
                            HCJoinWidth,
                            HCSingleLength,
                            HCSingleWidth,

                            StEDPageJoinLength,
                            StEDPageJoinWidth,
                            StEDPageSplitSize,
                            OthersLineQty,
                            HCSplitWeight,
                            HCSplitSet,
                            HCSplitHeight,
                            HCSplitQty
";               
            }

        }
        string strLabourCost = @"RowSeqNo,BOMSeqNo ,SeqNo,WKID,WKName,WKPriceType,WKRemark,CategoryID,CategoryName,SetID,SetName,VersionNo,ProductFItemID,ProductName,FItemID,ItemNo,TechnicsName,
                                 CalUnit ,	CutType ,  WorkQty ,  TotalQty,	StandardQty ,	SpendHours,	StandardWage ,	StandardWage26 ,	UnitCost,TotUnitCost,Remark  ,
                                 DetailCost,WorkCost, DetailTotWorkCost,IsKeyWK  ,WKOutName
                                ";

        string strDetails = @"RowSeqNo ,SeqNo,VersionNo,ProductFItemID,ProductName,WKID,WKName,WKRemark,CategoryID,CategoryName,SetID,SetName,TechnicsName,
                              IsKeyWK,IsInputWK,FItemID,  ItemNo, ItemName,SupNo,SupName, Price, PriceUnit,
                              Qty, WastageRate,DetailCost, RatedCost,LabourCost, TotalCost,WastageCost";

        public bool UpdateHdrbyBOMDetail(DBAccess dbcon, string strCategoryID, string strSetID,string strProductFItemID,string strTechnicsName, string strVersionNo)
        {
            double dblTotalDetailCost = 0;
            double dblTotalLabourCost = 0;
            double dblTotalWastageCost = 0;
            double dblTotalWorkCost = 0;
            string strFieldName = "CategoryID,CategoryName,SetID, SetName, ProductFItemID ,ProductName,TechnicsName,VersionNo";

            string strSQL = "select * from rmProductBOMRev where CategoryID='{0}' and SetID='{1}' and ProductFItemID='{2}' and VersionNo='{3}'";
            strSQL = string.Format(strSQL, strCategoryID, strSetID,strProductFItemID,strVersionNo);
            DataRow drHdr = dbcon.GetDataFirstRow(strSQL);
            if (drHdr == null)
            {
                return true;
                drHdr = dbcon.GetDataTable(strSQL).NewRow();
                drHdr["CategoryID"] = strCategoryID;
                drHdr["SetID"] = strSetID;
                drHdr["ProductFItemID"] = strProductFItemID;

                drHdr["CategoryName"]= DataFunctions.GetFieldValue (dbcon,"rmCategory","CategoryName","CategoryID='"+ strCategoryID  +"'");
                drHdr["SetName"]= DataFunctions.GetFieldValue (dbcon,"rmSet","SetName","SetID='"+ strSetID +"'");
                drHdr["ProductName"]= DataFunctions.GetFieldValue (dbcon,"rmProduct","ProductName","ProductFItemID='"+ strProductFItemID  +"'");

                drHdr["VersionNo"] = strVersionNo;
                drHdr["TechnicsName"] = strTechnicsName; 
                DataFunctions.UpdateTable(dbcon, strFieldName, "rmProductBOMRev", drHdr, FormStatus.AddNew);
            }
            else
            {
                DataFunctions.UpdateTable(dbcon, strFieldName, "rmProductBOMRev", drHdr, FormStatus.Update);
            }

            strSQL = "select sum(DetailCost),sum(LabourCost),sum(WastageCost) from rmProductBOMDetailRev where CategoryID='{0}' and SetID='{1}' and ProductFItemID='{2}' and VersionNo='{3}'";
            strSQL = string.Format(strSQL, strCategoryID, strSetID, strProductFItemID,strVersionNo);
            DataRow drTotal = dbcon.GetDataFirstRow(strSQL);
            if (drTotal != null)
            {
                dblTotalDetailCost = CvtFunc.ToDouble(drTotal[0]);               
                dblTotalWastageCost = CvtFunc.ToDouble(drTotal[2]);
            }

            strSQL = "select sum(DetailCost),sum(DetailTotWorkCost) from rmProductBOMLabourCostRev where CategoryID='{0}' and SetID='{1}' and ProductFItemID='{2}' and VersionNo='{3}'";
            strSQL = string.Format(strSQL, strCategoryID, strSetID, strProductFItemID,strVersionNo );
            drTotal = dbcon.GetDataFirstRow(strSQL);
            if (drTotal != null)
            {
                dblTotalLabourCost = CvtFunc.ToDouble(drTotal[0]);
                dblTotalWorkCost = CvtFunc.ToDouble(drTotal[1]);
            }

            double dblTotalCost = dblTotalDetailCost + dblTotalLabourCost + dblTotalWastageCost + dblTotalWorkCost ;
            double dblVerUnitCost=0;
            if (CvtFunc.ToDouble(drHdr["Quantity"]) > 0)
            {
                dblVerUnitCost = dblTotalCost / CvtFunc.ToDouble(drHdr["Quantity"]);
            }

            strSQL = @" update rmProductBOMRev set TotalDetailCostRev='{3}',TotalLabourCostRev='{4}',TotalWastageCostRev='{5}' ,TotalCostRev='{6}',TotalWorkCostRev='{7}'
                        where CategoryID='{0}' and SetID='{1}' and ProductFItemID='{2}' and VersionNo='{8}'";
            strSQL = string.Format(strSQL, strCategoryID, strSetID, strProductFItemID, dblTotalDetailCost, dblTotalLabourCost,
                                            dblTotalWastageCost.ToString ("f6"), dblTotalCost,dblTotalWorkCost,strVersionNo);
            dbcon.ExecuteSql(strSQL);

            if (strTechnicsName == "")
            {
                strTechnicsName = DataFunctions.GetFieldValue(dbcon, "rmProductBOMRev", "TechnicsName", "CategoryID='"+ strCategoryID +"' and SetID='"+ strSetID +"' and ProductFItemID='"+ strProductFItemID  +"' and VersionNo='"+ strVersionNo +"'");
            }
            double dblTotalDetailCostSTD = 0, dblTotalLabourCostSTD = 0, dblTotalWastageCostSTD = 0, dblTotalCostSTD = 0, dblTotalWorkCostSTD = 0, dblStandardUnitCost=0;
            strSQL = "select * from rmProductBOMHdr where CategoryID='{0}' and SetID='{1}' and ProductFItemID='{2}' and TechnicsName='{3}'";
            strSQL = string.Format(strSQL, strCategoryID, strSetID, strProductFItemID, strTechnicsName);
            DataRow drRow = dbcon.GetDataFirstRow(strSQL);
            if (drRow != null)
            {
                dblTotalDetailCostSTD = CvtFunc.ToDouble ( drRow["TotalDetailCost"]);
                dblTotalLabourCostSTD = CvtFunc.ToDouble (drRow["TotalLabourCost"]) ;
                dblTotalWastageCostSTD = CvtFunc.ToDouble(drRow["TotalWastageCost"]);
                dblTotalCostSTD = CvtFunc.ToDouble(drRow["TotalCost"]);
                dblTotalWorkCostSTD = CvtFunc.ToDouble(drRow["TotalWorkCost"]);
                dblStandardUnitCost = CvtFunc.ToDouble(drRow["StandardUnitCost"]);
            }

            strSQL = @" update rmProductBOMRev set TotalDetailCostDiff='{3}',TotalLabourCostDiff='{4}',TotalWastageCostDiff='{5}' ,TotalCostDiff='{6}',TotalWorkCostDiff='{7}',TotalCost='{9}',
                        VerUnitCost='{10}',StandardUnitCost='{11}'     
                        where CategoryID='{0}' and SetID='{1}' and ProductFItemID='{2}' and VersionNo='{8}'";
            strSQL = string.Format(strSQL, strCategoryID, strSetID, strProductFItemID, (dblTotalDetailCost - dblTotalDetailCostSTD).ToString ("f2"), 
                        ( dblTotalLabourCost - dblTotalLabourCostSTD).ToString ("f2"),
                                            (dblTotalWastageCost - dblTotalWastageCostSTD).ToString("f2"),
                                            (dblTotalCost - dblTotalCostSTD).ToString("f2"),
                                            (dblTotalWorkCost - dblTotalWorkCostSTD).ToString("f2"), 
                                            strVersionNo,
                                            dblTotalCostSTD.ToString ("F8"),
                                            dblVerUnitCost.ToString("F4"),
                                            dblStandardUnitCost.ToString ("F4") );
            dbcon.ExecuteSql(strSQL);
            


            
            return true;
        }

        public bool UpdateBOMDetail(DBAccess dbcon, DataRow drBOM)
        {
            return UpdateBOMDetail( dbcon,  drBOM, false );
        }

        public bool UpdateBOMDetail(DBAccess dbcon, DataRow drBOM,bool blnNotUpdateHead)
        {
            try
            {                               
                if (!UpdateCost(dbcon, drBOM)) return false;
                if (CvtFunc.ToDouble(drBOM["RecID"]) > 0)
                {
                    DataFunctions.UpdateTable(dbcon, strDetails, "rmProductBOMDetailRev", drBOM, FormStatus.Update);
                }
                else
                {
                    DataFunctions.UpdateTable(dbcon, strDetails, "rmProductBOMDetailRev", drBOM, FormStatus.AddNew);
                }
                if (!blnNotUpdateHead) UpdateHdrbyBOMDetail(dbcon, CvtFunc.ToString(drBOM["CategoryID"]), CvtFunc.ToString(drBOM["SetID"]), CvtFunc.ToString(drBOM["ProductFItemID"]), CvtFunc.ToString(drBOM["TechnicsName"]) ,CvtFunc.ToString ( drBOM["VersionNo"]));
            }
            catch (Exception ex)
            {
                MsgBoxEx.Show(ex.ToString());
                return false;
            }
            return true;
        }

        public bool UpdateCost(DBAccess dbcon, DataRow drBOM)
        {
            CalQty(dbcon, drBOM);
            CalQtyWastage(dbcon, drBOM);
            CalWastageCost(dbcon, drBOM);
            CalDetailCost(dbcon, drBOM);
            CalTotalCost(dbcon, drBOM);
            //UpdateHdrbyBOMDetail(dbcon, CvtFunc.ToString(drBOM["CategoryName"]), CvtFunc.ToString(drBOM["SetName"]), CvtFunc.ToString(drBOM["ProductName"]), CvtFunc.ToString(drBOM["VersionNo"]));
            return true;
        }

        public bool CalQty(DBAccess dbcon, DataRow drBOM)   //用于计算定额用量
        {
            string strWKFormula = "";
            string strSQL="select * from rmWKProductFormulaRev where BOMRecID='"+ CvtFunc.ToString ( drBOM["RecID"] ) +"'";
            DataRow drRow = dbcon.GetDataFirstRow(strSQL);
            if (drRow != null)
            {
                strWKFormula = CvtFunc.ToString(drRow["WKFormula"]);
                if (strWKFormula == "") return true; 
                strSQL = "select * from rmWKCalDict";
                DataTable dtDict = dbcon.GetDataTable(strSQL);
                foreach (DataRow drDict in dtDict.Rows)
                {
                    strWKFormula = strWKFormula.Replace(CvtFunc.ToString(drDict["CHNFieldName"]), CvtFunc.ToString(drDict["SYSTableName"]) + "." + CvtFunc.ToString(drDict["SYSFieldName"])); 
                }
                if (strWKFormula.IndexOf("ImCategoryWKRating", 0, StringComparison.OrdinalIgnoreCase) >= 0)
                {
                    double dblQtyRate = 0;
                    //strSQL = "select * from ImCategoryWKRating where FItemID='{0}' and CategoryID='{1}' and WKProductID='{2}' and SupName='{3}'";
                    strSQL = "select * from ImCategoryWKRating where FItemID='{0}' and WKProductID='{2}' and SupName='{3}'";
                    strSQL = string.Format(strSQL, drBOM["FItemID"], drBOM["CategoryID"], drBOM["WKID"], drBOM["SupName"]);
                    DataRow drRateQty = dbcon.GetDataFirstRow(strSQL);
                    if (drRateQty != null)
                    {
                        dblQtyRate = CvtFunc.ToDouble(drRateQty["RateQty"]);
                    }
                    strWKFormula = strWKFormula.Replace("ImCategoryWKRating.RatingQty", dblQtyRate.ToString());
                }
                strWKFormula = strWKFormula.Replace("rmProduct", "rmProductBOMRev");
                if (strWKFormula.IndexOf("iminvmaster", 0, StringComparison.OrdinalIgnoreCase) < 0)
                {
                    strSQL = @" select {0} from rmProductBOMRev where  rmProductBOMRev.ProductFItemID='{2}' and rmProductBOMRev.CategoryID='{3}' and rmProductBOMRev.SetID='{4}' and versionno='{5}'";
                    strSQL = string.Format(strSQL, strWKFormula, drBOM["FItemID"], drBOM["ProductFItemID"], drBOM["CategoryID"], drBOM["SetID"],drBOM["VersionNo"]);
                    DataRow drQty = dbcon.GetDataFirstRow(strSQL);
                    drBOM["Qty"] = CvtFunc.ToDouble(drQty[0]);
                }
                else
                {
                    strSQL = @" select {0}   from rmProductBOMRev
                          ,iminvmaster where FItemID='{1}' and rmProductBOMRev.ProductFItemID='{2}' and rmProductBOMRev.CategoryID='{3}' and rmProductBOMRev.SetID='{4}' and iminvmaster.SupNo='{5}' and versionno='{6}'";
                    strSQL = string.Format(strSQL, strWKFormula, drBOM["FItemID"], drBOM["ProductFItemID"], drBOM["CategoryID"], drBOM["SetID"], drBOM["SupNo"],drBOM["Versionno"] );
                    DataRow drQty = dbcon.GetDataFirstRow(strSQL);
                    drBOM["Qty"] = CvtFunc.ToDouble(drQty[0]);
                }
            } 
            return true;
        }

        public bool CalLabourCostQty(DBAccess dbcon, DataRow drBOM)   //用于计算定额用量
        {
            string strWKFormula = "";
            string strSQL = "select * from rmWKFormulaOtherProductBOMRev where BOMRecID='" + CvtFunc.ToString(drBOM["RecID"]) + "'";
            DataRow drRow = dbcon.GetDataFirstRow(strSQL);
            if (drRow != null)
            {
                strWKFormula = CvtFunc.ToString(drRow["WKFormula"]);
                strSQL = "select * from rmWKCalDict";
                DataTable dtDict = dbcon.GetDataTable(strSQL);
                foreach (DataRow drDict in dtDict.Rows)
                {
                    strWKFormula = strWKFormula.Replace(CvtFunc.ToString(drDict["CHNFieldName"]), CvtFunc.ToString(drDict["SYSTableName"]) + "." + CvtFunc.ToString(drDict["SYSFieldName"]));
                }
                if (strWKFormula.IndexOf("ImCategoryWKRating", 0, StringComparison.OrdinalIgnoreCase) >= 0)
                {
                    double dblQtyRate = 0;
                    //strSQL = "select * from ImCategoryWKRating where itemno='{0}' and CategoryID='{1}' and WKProductID='{2}' and SupName='{3}'";
                    strSQL = "select * from ImCategoryWKRating where itemno='{0}' and WKProductID='{2}' and SupName='{3}'";
                    strSQL = string.Format(strSQL, drBOM["ItemNo"], drBOM["CategoryID"], drBOM["WKID"], drBOM["SupName"]);
                    DataRow drRateQty = dbcon.GetDataFirstRow(strSQL);
                    if (drRateQty != null)
                    {
                        dblQtyRate = CvtFunc.ToDouble(drRateQty["RateQty"]);
                    }
                    strWKFormula = strWKFormula.Replace("ImCategoryWKRating.RatingQty", dblQtyRate.ToString());
                }
                strWKFormula = strWKFormula.Replace("rmProduct", "rmProductBOMRev");
                if (strWKFormula.IndexOf("iminvmaster",0, StringComparison.OrdinalIgnoreCase)<0)
                {
                    strSQL = @" select {0}   from rmProductBOMRev
                          where ProductFItemID='{2}' and CategoryName='{3}' and SetName='{4}' and versionno='{5}'";
                    strSQL = string.Format(strSQL, strWKFormula, drBOM["ItemNo"], drBOM["ProductFItemID"], drBOM["CategoryID"], drBOM["SetID"],drBOM["versionno"]);
                }
                else
                {
                    strSQL = @" select {0}   from rmProductBOMRev
                          ,iminvmaster where FItemID='{1}' and ProductFItemID='{2}' and CategoryID='{3}' and SetID='{4}' and versionno='{5}' ";
                    strSQL = string.Format(strSQL, strWKFormula, drBOM["FItemID"], drBOM["ProductFItemID"], drBOM["CategoryID"], drBOM["SetID"], drBOM["versionno"]);
                }
                DataRow drQty = dbcon.GetDataFirstRow(strSQL);
                drBOM["WorkQty"] = CvtFunc.ToDouble(drQty[0]);

                double dblCutType = 0;
                string strCutType = CvtFunc.ToString(drBOM["CutType"]);
                dblCutType = CvtFunc.ToDouble(DataFunctions.GetFieldValue("rmCutType", "CutTimes", "CutType='" + strCutType + "'"));
                if (dblCutType == 0)
                {
                    dblCutType = CvtFunc.ToDouble(strCutType);
                }
                if (dblCutType != 0)
                {
                    drBOM["TotalQty"] = CvtFunc.ToDouble(drBOM["WorkQty"]) / dblCutType;
                }
                else
                {
                    drBOM["TotalQty"] = CvtFunc.ToDouble(drBOM["WorkQty"]);
                }
            }
            return true;
        }

        public bool CalQtyWastage(DBAccess dbcon, DataRow drBOM)  //用于计算损耗数量
        {
            drBOM["QtyWastage"] = CvtFunc.ToDouble(drBOM["WastageRate"]) * CvtFunc.ToDouble(drBOM["Qty"]) * 0.01;
            return true;
        }

        public bool CalWastageCost(DBAccess dbcon, DataRow drBOM)  //用于计算损耗成本
        {
            drBOM["WastageCost"] = CvtFunc.ToDouble(drBOM["QtyWastage"]) * CvtFunc.ToDouble(drBOM["Price"]);
            return true;
        }

        public bool CalDetailCost(DBAccess dbcon, DataRow drBOM)  //用于计算定额成本
        {
            drBOM["DetailCost"] = CvtFunc.ToDouble(drBOM["Qty"]) * CvtFunc.ToDouble(drBOM["Price"]);
            return true;
        }

        public bool CalTotalCost(DBAccess dbcon, DataRow drBOM)  //用于计算总成本
        {
            drBOM["TotalCost"] = CvtFunc.ToDouble(drBOM["DetailCost"]) + CvtFunc.ToDouble(drBOM["LabourCost"]) + CvtFunc.ToDouble(drBOM["WastageCost"]);                              
            return true;
        }


        public bool DeleteDetailBOM( DBAccess dbcon, string strRecID)
        {
            string strSQL = "delete from rmProductBOMDetailRev where recID='" + strRecID + "'";
            dbcon.ExecuteSql(strSQL);

            strSQL = "delete from rmWKProductFormulaRev where BOMRecID='" + strRecID + "'";
            dbcon.ExecuteSql(strSQL);
            return true;
        }

        public bool DeleteDetail_LabourCost(DBAccess dbcon, string strRecID)
        {
            string strSQL = "delete from rmProductBOMLabourCostRev where recID='" + strRecID + "'";
            dbcon.ExecuteSql(strSQL);

            strSQL = "delete from rmWKFormulaOtherProductBOMRev where BOMRecID='" + strRecID + "'";
            dbcon.ExecuteSql ( strSQL );
            return true;
        }

        #region OtherCost Grid
        public bool UpdateLabourCostDetail(DBAccess dbcon, DataRow drLabourCost)
        {
            return UpdateLabourCostDetail(dbcon, drLabourCost, false);
        }

        public bool UpdateLabourCostDetail(DBAccess dbcon, DataRow drLabourCost,bool blnNotUpdateHead)
        {
            try
            {
                CalLabourCostQty(dbcon, drLabourCost);
                CalUnitCost(dbcon, drLabourCost);
                CalSpendHours(dbcon, drLabourCost); 
                CalLabourCostDetailCost(dbcon, drLabourCost);                
                CalWorkCostDetailCost(dbcon, drLabourCost);
                if (CvtFunc.ToDouble(drLabourCost["RecID"]) > 0)
                {
                    DataFunctions.UpdateTable(dbcon, strLabourCost, "rmProductBOMLabourCostRev", drLabourCost, FormStatus.Update);
                }
                else
                {
                    DataFunctions.UpdateTable(dbcon, strLabourCost, "rmProductBOMLabourCostRev", drLabourCost, FormStatus.AddNew);
                }
                if (!blnNotUpdateHead) UpdateHdrbyBOMDetail(dbcon, CvtFunc.ToString(drLabourCost["CategoryID"]), CvtFunc.ToString(drLabourCost["SetID"]), CvtFunc.ToString(drLabourCost["ProductFItemID"]), CvtFunc.ToString(drLabourCost["TechnicsName"]) , CvtFunc.ToString ( drLabourCost["VersionNo"]));
            }
            catch (Exception ex)
            {
                MsgBoxEx.Show(ex.ToString());
                return false;
            }
            return true;
        }

        public void CalUnitCost(DBAccess dbcon, DataRow drLabourCost)
        {
            double dblStandardQty = CvtFunc.ToDouble(drLabourCost["StandardQty"]);      //额定产量
            double dblStandardWage = CvtFunc.ToDouble(drLabourCost["StandardWage"]);    //固定工资  
            double dblStandardWage26 = CvtFunc.ToDouble(drLabourCost["StandardWage26"]);    //26天标准工资 
            double dblQty = CvtFunc.ToDouble(drLabourCost["TotalQty"]);
            if (dblStandardQty != 0)
            {
                double dblUnitCost = (dblStandardWage26 - dblStandardWage / 22d * 26) / dblStandardQty / 26d;
                drLabourCost["UnitCost"] = dblUnitCost.ToString("f6");
                drLabourCost["TotUnitCost"] = (dblUnitCost * dblQty).ToString("f4");                
            }
            else
            {
                drLabourCost["UnitCost"] = 0;
            }
        }
        public void CalSpendHours(DBAccess dbcon, DataRow drLabourCost)
        {
            double dblTotalQty = CvtFunc.ToDouble ( drLabourCost["TotalQty"]);
            double dblStandardQty = CvtFunc.ToDouble(drLabourCost["StandardQty"]);
            if (dblStandardQty != 0)
            {
                drLabourCost["SpendHours"] = dblTotalQty / dblStandardQty;
            }
            else
                drLabourCost["SpendHours"] = "0";
        }

        public void CalLabourCostDetailCost(DBAccess dbcon, DataRow drLabourCost) //人工成本 = 单位总工价*计量数量 
        {
            double dblStandardQty = CvtFunc.ToDouble(drLabourCost["StandardQty"]);      //额定产量
            double dblTotalQty = CvtFunc.ToDouble(drLabourCost["TotalQty"]);            //数量    
            double dblStandardWage26 = CvtFunc.ToDouble(drLabourCost["StandardWage26"]);    //26天标准工资             
            double dblUnitCost = 0;
            if (dblStandardQty == 0)
                dblUnitCost = 0;
            else
                dblUnitCost = dblTotalQty * (dblStandardWage26 / 26d / dblStandardQty);  //总工价                        
            double dblDetailCost = 0;

            dblDetailCost = dblUnitCost ;
            drLabourCost["DetailCost"] = dblDetailCost;
        }

        public void CalWorkCostDetailCost(DBAccess dbcon, DataRow drLabourCost)   //用于计算项目分摊
        {
            double dblTotWorkCost = CvtFunc.ToDouble(drLabourCost["SpendHours"]) * CvtFunc.ToDouble(drLabourCost["WorkCost"]);
            drLabourCost["DetailTotWorkCost"] = dblTotWorkCost;
        }
        #endregion


        public bool CopytoNewVer(DBAccess dbcon, DataRow drOldHdr)
        {            
            try
            {
                // init HDR
                if (drOldHdr == null) return false;
                string strCategoryName = "", strSetName = "", strProductName = "", strTechnicsName="",strOldVersion = "";
                string strCategoryID = "", strSetID = "",strProductFItemID="";
                
                strCategoryID = CvtFunc.ToString(drOldHdr["CategoryID"]);
                strCategoryName = DataFunctions.GetFieldValue ( dbcon,"rmCategory","CategoryName","CategoryID='"+ CvtFunc.ToString(drOldHdr["CategoryID"]) +"'");

                strSetID = CvtFunc.ToString(drOldHdr["SetID"]);
                strSetName =  DataFunctions.GetFieldValue ( dbcon,"rmSet","SetName","SetID='"+ CvtFunc.ToString(drOldHdr["SetID"]) +"'");
                                
                strProductFItemID = CvtFunc.ToString(drOldHdr["ProductFItemID"]);
                strProductName =  DataFunctions.GetFieldValue ( dbcon,"rmProduct","ProductName","ProductFItemID='"+ drOldHdr["ProductFItemID"] +"'");
                
                strOldVersion = CvtFunc.ToString(drOldHdr["VersionNo"]);
                strTechnicsName = CvtFunc.ToString(drOldHdr["TechnicsName"]);

                string strNewVisionNo = CvtFunc.ToString(CvtFunc.ToDouble(DataFunctions.GetFieldValue(dbcon, "rmProductBOMRev", "max(VersionNo)", "productFItemID='" + strProductFItemID + "'")) + 1);

                DataRow drHdr = dbcon.GetDataTable("select top 0 * from rmProductBOMRev").NewRow();
                {
                   
                    drHdr["CategoryName"] = strCategoryName;
                    drHdr["CategoryID"] = strCategoryID;
                    drHdr["SetName"] = strSetName;
                    drHdr["SetID"] = strSetID; 
                    drHdr["ProductName"] = strProductName;
                    drHdr["ProductFitemID"] = strProductFItemID;  
                    drHdr["TechnicsName"] = strTechnicsName; 
                    drHdr["VersionNo"] = strNewVisionNo;
                    //drHdr["Creator"] =  SysVar.UserName;  //drOldHdr["Creator"].ToString();
                    //drHdr["CreateDate"] = DateTime.Now.Date;  // drOldHdr["CreateDate"];

                    foreach (DataColumn drCol in drOldHdr.Table.Columns)
                    {
                        if (drCol.ToString().Trim().ToUpper() == "RECID" || drCol.ToString().Trim().ToUpper() == "RECSTAMP" || drCol.ToString().Trim ().ToUpper ()=="VERSIONNO")
                        {
                        }
                        else 
                        {
                            drHdr[drCol.ToString()] = drOldHdr[drCol.ToString()];
                        }
                    }
                    drHdr["Creator"] = SysVar.UserName;  //drOldHdr["Creator"].ToString();
                    drHdr["CreateDate"] = DateTime.Now.Date;  // drOldHdr["CreateDate"];
                    DataFunctions.UpdateTable(dbcon, Fields , "rmProductBOMRev", drHdr, FormStatus.AddNew);
                    DataFunctions.UpdateTable(dbcon, ParameterFields, "rmProductBOMRev", drHdr, FormStatus.Update);
                }

                string strSQL = "select A.*, '" + strProductName + "' as ProductName  from rmProductBOMDetailRev A  where A.CategoryID='{0}' and A.SetID='{1}' and A.ProductFItemID='{2}' and VersionNo='{3}'  order by A.RowSeqNo";
                strSQL = string.Format(strSQL, strCategoryID, strSetID, strProductFItemID, strOldVersion);
                DataTable dtOld = dbcon.GetDataTable(strSQL);
                foreach (DataRow dr in dtOld.Rows)
                {
                    DataRow drBOM = dbcon.GetDataSet("select top 0 * from rmProductBOMDetailRev").Tables[0].NewRow();

                    drBOM["SeqNo"] = dr["SeqNo"].ToString().TrimEnd();
                    drBOM["RowSeqNo"] = dr["RowSeqNo"].ToString().Trim();
                    drBOM["WKID"] = dr["WKID"].ToString().TrimEnd();
                    drBOM["WKName"] =DataFunctions.GetFieldValue (dbcon,"rmWorkingProcedure","WorkingProcedureName","WorkingProcedureID='"+ dr["WKID"] +"'");
                    drBOM["VersionNo"] = strNewVisionNo;
                    drBOM["ProductFItemID"] = dr["ProductFItemID"].ToString().TrimEnd();
                    drBOM["ProductName"] = dr["ProductName"].ToString().TrimEnd();
                    drBOM["TechnicsName"] = dr["TechnicsName"].ToString().TrimEnd();
                    
                    drBOM["CategoryID"] = dr["CategoryID"].ToString().TrimEnd();
                    drBOM["SetID"] = CvtFunc.ToString(dr["SetID"]);
                    drBOM["FItemID"] = CvtFunc.ToString(dr["FItemID"]);

                    drBOM["CategoryName"] =strCategoryName ;
                    drBOM["SetName"] = strSetName ;
                    drBOM["ItemNo"] = DataFunctions.GetFieldValue (dbcon ,"imInvMaster","ItemNo","FItemID='"+ dr["FItemID"] +"'") ;
                    drBOM["ItemName"] = DataFunctions.GetFieldValue(dbcon, "imInvMaster", "ItemName", "FItemID='" + dr["FItemID"] + "'");
                    drBOM["IsKeyWK"] = CvtFunc.ToBoolean(dr["IsKeyWK"]);
                    
                    drBOM["SupNo"] = CvtFunc.ToString(dr["SupNo"]);
                    drBOM["SupName"] = CvtFunc.ToString(dr["SupName"]);
                    drBOM["LabourCost"] = BaseFunctions.FormatNumeric(dr["LabourCost"], "Ns.4");
                    drBOM["Price"] = CvtFunc.ToDouble  ( DataFunctions.GetFieldValue ( dbcon, "iminvmaster","MarketCost","itemno='"+ drBOM["ItemNo"].ToString()  +"' and supno='"+ drBOM ["SupNo"].ToString () +"'"))  ; //BaseFunctions.FormatNumeric(dr["Price"], "Ns.3");
                    drBOM["PriceUnit"] = CvtFunc.ToString(dr["PriceUnit"]);
                    drBOM["Qty"] = BaseFunctions.FormatNumeric(CvtFunc.ToDouble(dr["Qty"]).ToString("F8"), "Ns.8");
                    drBOM["QtyWastage"] = BaseFunctions.FormatNumeric(dr["QtyWastage"], "Ns.2");
                    drBOM["WastageRate"] = BaseFunctions.FormatNumeric(dr["WastageRate"], "Ns.2");
                    drBOM["TotalCost"] = BaseFunctions.FormatNumeric(dr["TotalCost"], "Ns.2");
                    drBOM["DetailCost"] = BaseFunctions.FormatNumeric(dr["DetailCost"], "Ns.4");
                    drBOM["IsInputWK"] = dr["IsInputWK"].ToString();
                    drBOM["IsKeyWK"] = dr["IsKeyWK"].ToString();
                    if (!UpdateBOMDetail(dbcon, drBOM,true))
                    {
                        return false;
                    }

                    // Init  rmWKProductFormula  
                    strSQL = "select * from rmWKProductFormulaRev where BOMRecID='" + CvtFunc.ToString(dr["RecID"]) + "'";
                    DataTable dtFormula = dbcon.GetDataTable(strSQL);
                    foreach (DataRow drFormula in dtFormula.Rows)
                    {
                        strSQL = @"Insert Into rmWKProductFormulaRev (BOMRecID,ProductName,WKFormula)
                              VALUES ('{0}','{1}','{2}')";
                        strSQL = string.Format(strSQL, drBOM["RecID"], drBOM["ProductName"], drFormula["WKFormula"]);
                        dbcon.ExecuteSql(strSQL);
                    }
                    if (!UpdateBOMDetail(dbcon, drBOM, false ))
                    {
                        return false;
                    }
                }

                strSQL = "select A.*,'" + strProductName + "' as ProductName from rmProductBOMLabourCostRev A where A.CategoryID='{0}' and A.SetID='{1}' and A.ProductFItemID='{2}' and A.VersionNo='{3}' order by a.RowSeqNo ";
                strSQL = string.Format(strSQL, strCategoryID, strSetID, strProductFItemID, strOldVersion);
                dtOld = dbcon.GetDataTable(strSQL);
                foreach (DataRow dr in dtOld.Rows)
                {
                    DataRow drBOM = dbcon.GetDataSet("select  top 0 * from rmProductBOMLabourCostRev").Tables[0].NewRow();

                    drBOM["SeqNo"] = dr["SeqNo"].ToString().TrimEnd();
                    drBOM["RowSeqNo"] = dr["RowSeqNo"].ToString().Trim();
                    drBOM["WKID"] = dr["WKID"].ToString().TrimEnd();
                    drBOM["WKName"] = DataFunctions.GetFieldValue(dbcon, "rmWorkingProcedure", "WorkingProcedureName", "WorkingProcedureID='" + dr["WKID"] + "'");
                    drBOM["WKOutName"] = DataFunctions.GetFieldValue(dbcon, "rmWorkingProcedure", "WKout", "WorkingProcedureID='" + dr["WKID"] + "'");
                    drBOM["WKPriceType"]= drBOM["WKPriceType"].ToString().TrimEnd();   
                    drBOM["ProductFItemID"] = dr["ProductFItemID"].ToString().TrimEnd();
                    drBOM["CategoryID"] = dr["CategoryID"].ToString().TrimEnd();
                    drBOM["SetID"] = CvtFunc.ToString(dr["SetID"]);
                    drBOM["IsKeyWK"] = dr["IsKeyWK"].ToString(); 
                    drBOM["ProductName"] = strProductName ;
                    drBOM["CategoryName"] = strCategoryName  ;
                    drBOM["TechnicsName"] = dr["TechnicsName"].ToString().TrimEnd();   
                    drBOM["SetName"] = strSetName  ;
                    drBOM["VersionNo"] = strNewVisionNo;
                    drBOM["CalUnit"] = CvtFunc.ToString(dr["CalUnit"]);
                    drBOM["CutType"] = CvtFunc.ToString(dr["CutType"]);
                    drBOM["TotalQty"] = BaseFunctions.FormatNumeric(dr["TotalQty"], "Ns.4");
                    drBOM["StandardQty"] = BaseFunctions.FormatNumeric(dr["StandardQty"], "Ns.3");
                    drBOM["SpendHours"] = CvtFunc.ToString(dr["SpendHours"]);
                    drBOM["StandardWage"] = BaseFunctions.FormatNumeric(dr["StandardWage"], "Ns.2");
                    drBOM["StandardWage26"] = BaseFunctions.FormatNumeric(dr["StandardWage26"], "Ns.2");
                    drBOM["UnitCost"] = BaseFunctions.FormatNumeric(dr["UnitCost"], "Ns.2");                    
                    drBOM["Remark"] = CvtFunc.ToString(dr["Remark"]);

                    string strWKPriceType = CvtFunc.ToString(drBOM["WKPriceType"]);
                    strSQL = "select * from [imCategoryWKWorkCost] where CategoryID='{0}' and WKProductID='{1}' and WKPriceType='{2}'";
                    strSQL = string.Format(strSQL, CvtFunc.ToString(drBOM["CategoryID"]), CvtFunc.ToString(drBOM["WKID"]), strWKPriceType);
                    dbcon = new DBAccess(false, false);
                    DataRow drRow = dbcon.GetDataFirstRow(strSQL);
                    if (drRow != null)
                    {
                        drBOM["CalUnit"] = CvtFunc.ToString(drRow["STDQtyUnit"]);
                        drBOM["StandardQty"] = BaseFunctions.FormatNumeric(drRow["WKSTDQty"], "Ns.2");
                        drBOM["StandardWage"] = BaseFunctions.FormatNumeric(drRow["WKSalary"], "Ns.2");
                        drBOM["StandardWage26"] = BaseFunctions.FormatNumeric(drRow["WKSTDSalary"], "Ns.2");
                        drBOM["UnitCost"] = BaseFunctions.FormatNumeric(drRow["WKUnitCost"], "Ns.6");
                        drBOM["WorkCost"] = CvtFunc.ToString(drRow["WorkCostRate"]);
                    }
                    drBOM["ItemNo"] = CvtFunc.ToString(dr["ItemNo"]);
                    if (!UpdateLabourCostDetail(dbcon, drBOM,true ))
                    {
                        return false;
                    }
                    // Init  rmWKProductFormula  
                    strSQL = "select * from rmWKFormulaOtherProductBOMRev where BOMRecID='" + CvtFunc.ToString(dr["RecID"]) + "'";
                    DataTable dtFormula = dbcon.GetDataTable(strSQL);
                    foreach (DataRow drFormula in dtFormula.Rows)
                    {
                        strSQL = @"Insert Into rmWKFormulaOtherProductBOMRev (BOMRecID,ProductName,WKFormula)
                              VALUES ('{0}','{1}','{2}')";
                        strSQL = string.Format(strSQL, drBOM["RecID"], drBOM["ProductName"], drFormula["WKFormula"]);
                        dbcon.ExecuteSql(strSQL);
                    }
                    if (!UpdateLabourCostDetail(dbcon, drBOM, false ))
                    {
                        return false;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                MsgBoxEx.Show(ex.ToString());
                return false;
            }

        }
    }
}
